﻿Imports MySql.Data.MySqlClient
Imports MySql.Data.Types
Public Class EMySQLQueryException
    Inherits Exception
    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub New(ByVal Message As String)
        MyBase.New(Message)
    End Sub
    Public Sub New(ByVal Message As String, ByVal ex As Exception)
        MyBase.New(Message, ex)
    End Sub
End Class
Public Class TMyQuery
    Implements System.IDisposable
    Public Enum TColSeparator As Integer
        csComma = 0
        csTab = 1
    End Enum
    Private _AffectedRows As Integer = 0
    Private _Session As TMySession
    Private _SQL As New System.Text.StringBuilder
    Private _Command As New MySqlCommand
    Private _Eof As Boolean
    Private _DataAdapter As MySqlDataAdapter
    Private _DataSet As DataSet
    Private _Index As Integer

    Private disposedValue As Boolean = False        ' 重複する呼び出しを検出するには

    Public Enum TSQLType As Integer
        stSelect = 1
        stInsert = 2
        stUpdate = 3
        stDelete = 4
        stReplace = 5
        stCreate = 6
        stAlter = 7
        stDrop = 8
        stTruncate = 9
        stEtc = 10
    End Enum

    Public ReadOnly Property Command() As MySqlCommand
        Get
            Return _Command
        End Get
    End Property

    Public Sub DeleteVariable(ByVal AVariableName As String)
        Try
            _Command.Parameters.RemoveAt(AVariableName)
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeleteVariable(ByVal AIndex As Integer)
        Try
            _Command.Parameters.RemoveAt(AIndex)
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub
    Public Sub DeleteVariables()
        Try
            _Command.Parameters.Clear()
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeclareVariable(ByVal AVariableName As String)
        Try
            Dim param As New MySqlParameter(AVariableName, Nothing)
            _Command.Parameters.Add(param)
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub SetVariable(ByVal AVariableName As String, ByVal AValue As Object)
        Try
            _Command.Parameters(AVariableName).Value = AValue
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub SetVariable(ByVal AIndex As Integer, ByVal AValue As Object)
        Try
            _Command.Parameters(AIndex).Value = AValue
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeclareAndSet(ByVal AVariableName As String, ByVal AValue As Object)
        Try
            _Command.Parameters.AddWithValue(AVariableName, AValue)
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property SQL() As System.Text.StringBuilder
        Get
            Return _SQL
        End Get
    End Property

    Public Sub Close()
        Try
            If Not _DataSet Is Nothing Then
                _DataSet.Dispose()
            End If
            If Not _DataAdapter Is Nothing Then
                _DataAdapter.Dispose()
            End If
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub Clear()
        Try
            _SQL.Length = 0
            Me.DeleteVariables()
            _Command.CommandText = String.Empty
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property DataSet() As DataSet
        Get
            Return _DataSet
        End Get
    End Property

    Public ReadOnly Property DataSetText(ByVal AColSeparator As TColSeparator) As String
        Get

            Dim sbRow As New System.Text.StringBuilder
            Dim sb As New System.Text.StringBuilder
            Dim dt As DataTable = _DataSet.Tables(0)
            Dim colCount As Integer = dt.Columns.Count
            Dim lastColIndex As Integer = colCount - 1
            If dt Is Nothing Then
                Return String.Empty
            End If

            For i As Integer = 0 To colCount - 1
                Dim field As String = dt.Columns(i).Caption
                If AColSeparator = TColSeparator.csComma Then
                    '"で囲む必要があるか調べる
                    If field.IndexOf(ControlChars.Quote) > -1 OrElse _
                        field.IndexOf(","c) > -1 OrElse _
                        field.IndexOf(ControlChars.Cr) > -1 OrElse _
                        field.IndexOf(ControlChars.Lf) > -1 OrElse _
                        field.StartsWith(" ") OrElse _
                        field.StartsWith(ControlChars.Tab) OrElse _
                        field.EndsWith(" ") OrElse _
                        field.EndsWith(ControlChars.Tab) Then
                        If field.IndexOf(ControlChars.Quote) > -1 Then
                            '"を""とする
                            field = field.Replace("""", """""")
                        End If
                        field = """" + field + """"
                    End If
                    'フィールドを書き込む
                    sbRow.Append(field)
                    'カンマを書き込む
                    If lastColIndex > i Then
                        sbRow.Append(","c)
                    End If
                Else
                    sbRow.Append(field)
                    If lastColIndex > i Then
                        sbRow.Append(ControlChars.Tab)
                    End If
                End If
            Next i
            '改行する
            sb.AppendLine(sbRow.ToString)

            'レコードを書き込む
            Dim row As DataRow
            For Each row In dt.Rows
                sbRow.Length = 0
                For i As Integer = 0 To colCount - 1
                    'フィールドの取得
                    Dim field As String = row(i).ToString()
                    If AColSeparator = TColSeparator.csComma Then
                        '"で囲む必要があるか調べる
                        If field.IndexOf(ControlChars.Quote) > -1 OrElse _
                            field.IndexOf(","c) > -1 OrElse _
                            field.IndexOf(ControlChars.Cr) > -1 OrElse _
                            field.IndexOf(ControlChars.Lf) > -1 OrElse _
                            field.StartsWith(" ") OrElse _
                            field.StartsWith(ControlChars.Tab) OrElse _
                            field.EndsWith(" ") OrElse _
                            field.EndsWith(ControlChars.Tab) Then
                            If field.IndexOf(ControlChars.Quote) > -1 Then
                                '"を""とする
                                field = field.Replace("""", """""")
                            End If
                            field = """" + field + """"
                        End If
                        'フィールドを書き込む
                        sbRow.Append(field)
                        'カンマを書き込む
                        If lastColIndex > i Then
                            sbRow.Append(","c)
                        End If
                    Else
                        sbRow.Append(field)
                        If lastColIndex > i Then
                            sbRow.Append(ControlChars.Tab)
                        End If
                    End If
                Next i
                '改行する
                sb.AppendLine(sbRow.ToString)
            Next row
            Return sb.ToString
        End Get
    End Property
    Public Property Session() As TMySession
        Get
            Return _Session
        End Get
        Set(ByVal value As TMySession)
            _Session = value
            _Command.Connection = value.Connection
        End Set
    End Property

    Public Sub Execute()
        Try
            If Not _Session.Connected Then
                _Session.Connected = True
            End If
            _AffectedRows = 0
            If Me.SQLType = TSQLType.stSelect Then
                ExecuteSelect()
            Else
                ExecSQL()
            End If
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Private Sub ExecSQL()
        Try
            _Command.CommandText = _SQL.ToString
            _AffectedRows = _Command.ExecuteNonQuery()
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Private Sub ExecuteSelect()
        Try
            _Command.CommandText = Me.SQL.ToString
            _DataAdapter = New MySqlDataAdapter
            _DataAdapter.SelectCommand = _Command
            _DataSet = New DataSet
            _DataAdapter.Fill(_DataSet)
            _AffectedRows = _DataSet.Tables(0).Rows.Count
            _Index = 0
        Catch ex As Exception
            Throw New EMySQLQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property Eof() As Boolean
        Get
            Return _Index >= _DataSet.Tables(0).Rows.Count
        End Get
    End Property

    Public Sub MoveNext()
        _Index += 1
    End Sub

    Public ReadOnly Property FieldAsDecimal(ByVal AFieldName As String) As Decimal
        Get
            Return CDec(_DataSet.Tables(0).Rows(_Index).Item(AFieldName).ToString)
        End Get
    End Property

    Public ReadOnly Property FieldAsDecimal(ByVal AFieldIndex As Integer) As Decimal
        Get
            Return CDec(_DataSet.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
        End Get
    End Property

    Public ReadOnly Property FieldAsString(ByVal AFieldName As String) As String
        Get
            Return _DataSet.Tables(0).Rows(_Index).Item(AFieldName).ToString
        End Get
    End Property
    Public ReadOnly Property FieldAsString(ByVal AFieldIndex As Integer) As String
        Get
            Return _DataSet.Tables(0).Rows(_Index).Item(AFieldIndex).ToString
        End Get
    End Property

    Public ReadOnly Property FieldAsDateTime(ByVal AFieldName As String) As DateTime
        Get
            Try
                Return DateTime.Parse(_DataSet.Tables(0).Rows(_Index).Item(AFieldName).ToString)
            Catch ex As Exception
                Return DateTime.MinValue
            End Try
        End Get
    End Property

    Public ReadOnly Property FieldAsDateTime(ByVal AFieldIndex As Integer) As DateTime
        Get
            Try
                Return DateTime.Parse(_DataSet.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
            Catch ex As Exception
                Return DateTime.MinValue
            End Try
        End Get
    End Property

    Public ReadOnly Property FieldAsInteger(ByVal AFieldName As String) As Integer
        Get
            Try
                Return CInt(_DataSet.Tables(0).Rows(_Index).Item(AFieldName).ToString)
            Catch ex As Exception
                Return Nothing
            End Try
        End Get
    End Property
    Public ReadOnly Property FieldAsInteger(ByVal AFieldIndex As Integer) As Integer
        Get
            Try
                Return CInt(_DataSet.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
            Catch ex As Exception
                Return Nothing
            End Try
        End Get
    End Property

    Public ReadOnly Property SQLType() As TSQLType
        Get
            Dim s As String = _SQL.ToString.Trim.ToUpper
            If s.Substring(0, 6).Equals("SELECT") OrElse s.Substring(0, 4).Equals("SHOW") OrElse s.Substring(0, 7).Equals("EXPLAIN") Then
                Return TSQLType.stSelect
            ElseIf s.Substring(0, 4).Equals("INSERT") Then
                Return TSQLType.stInsert
            ElseIf s.Substring(0, 6).Equals("UPDATE") Then
                Return TSQLType.stUpdate
            ElseIf s.Substring(0, 6).Equals("DELETE") Then
                Return TSQLType.stDelete
            ElseIf s.Substring(0, 7).Equals("REPLACE") Then
                Return TSQLType.stReplace
            ElseIf s.Substring(0, 6).Equals("CREATE") Then
                Return TSQLType.stCreate
            ElseIf s.Substring(0, 5).Equals("ALTER") Then
                Return TSQLType.stAlter
            ElseIf s.Substring(0, 4).Equals("DROP") Then
                Return TSQLType.stDrop
            ElseIf s.Substring(0, 8).Equals("TRUNCATE") Then
                Return TSQLType.stTruncate
            Else
                Return TSQLType.stEtc
            End If

        End Get
    End Property

    Public ReadOnly Property AffectedRows() As Integer
        Get
            Return _AffectedRows
        End Get
    End Property
    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If
            If Not _DataSet Is Nothing Then
                _DataSet.Dispose()
            End If
            If Not _DataAdapter Is Nothing Then
                _DataAdapter.Dispose()
            End If
            _Command.Dispose()
            ' TODO: ユーザー独自の状態を解放します (アンマネージ オブジェクト)。
            ' TODO: 大きなフィールドを null に設定します。
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
